//package com.example.cloud_yuehong.modules1.controller;
//
//import com.alibaba.fastjson.JSONArray;
//import com.alibaba.fastjson.JSONObject;
//import com.example.cloud_yuehong.dto.Constants;
//import com.example.cloud_yuehong.model.CommonResult;
//import com.example.cloud_yuehong.modules1.entity.PdMember;
//import com.example.cloud_yuehong.modules1.service.PdMemberService;
//import com.example.cloud_yuehong.modules1.service.dto.PdMemberQueryCriteria;
//import com.example.cloud_yuehong.util.DateKit;
//import com.example.cloud_yuehong.util.ExcelUtils;
//import com.example.cloud_yuehong.util.UtilValidate;
//import io.swagger.annotations.Api;
//import io.swagger.annotations.ApiOperation;
//import io.swagger.annotations.ApiParam;
//import lombok.extern.slf4j.Slf4j;
//import org.apache.commons.io.IOUtils;
//import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFRow;
//import org.apache.poi.hssf.usermodel.HSSFSheet;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.ss.usermodel.CellType;
//import org.apache.poi.ss.usermodel.DataFormatter;
//import org.apache.poi.xssf.usermodel.XSSFCell;
//import org.apache.poi.xssf.usermodel.XSSFRow;
//import org.apache.poi.xssf.usermodel.XSSFSheet;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.web.bind.annotation.RequestMapping;
//import org.springframework.web.bind.annotation.RequestMethod;
//import org.springframework.web.bind.annotation.RequestParam;
//import org.springframework.web.bind.annotation.RestController;
//import org.springframework.web.multipart.MultipartFile;
//
//import javax.servlet.ServletContext;
//import javax.servlet.http.HttpServletRequest;
//import javax.servlet.http.HttpServletResponse;
//import java.io.*;
//import java.text.SimpleDateFormat;
//import java.util.*;
//
//import static com.example.cloud_yuehong.util.StringUtils.toUtf8String;
//
//@RestController
//@RequestMapping("/yh2/excel")
//@Api(tags = "excel")
//@Slf4j
//public class ExcelController {
//    @Autowired
//    PdMemberService pdMemberService;
//
//    @RequestMapping(value = "/export/memberData", method = {RequestMethod.GET})
//    @ApiOperation(value = "会员导出", notes = "会员导出")
//    public void memberData(PdMemberQueryCriteria pdMemberQueryCriteria, HttpServletRequest request, HttpServletResponse response) {
//        try{
//            List<PdMember> pdMembers = pdMemberService.queryAll(pdMemberQueryCriteria);
//            JSONArray list = new JSONArray();
//            Map<String, String> headMap = new LinkedHashMap<String, String>();
//            headMap.put("memberName", "会员名称");
//            headMap.put("qq", "qq");
//            headMap.put("mobile", "手机");
//            headMap.put("groupName", "群组");
//            headMap.put("address", "地址");
//            headMap.put("remark", "备注");
//            headMap.put("status", "状态");
//            headMap.put("createTime", "创建时间");
//            for (int i = 0; i < pdMembers.size(); i++) {
//                PdMember pdMember = pdMembers.get(i);
//                JSONObject json=new JSONObject();
//                json.put("memberName",pdMember.getMemberName());
//                json.put("qq",pdMember.getQq());
//                json.put("mobile",pdMember.getMobile());
//                json.put("groupName",pdMember.getGroupName());
//                json.put("address",pdMember.getAddress());
//                String status=pdMember.getStatus()== Constants.STATUS_ACTIVE?"正常":"异常";
//                json.put("status",status);
//                json.put("remark",pdMember.getRemark());
//                String createTime= DateKit.dateFormat((Date) pdMember.getCreateTime());
//                System.out.println("----createTime:"+createTime);
//                json.put("createTime", createTime);
//                list.add(json);
//            }
//            String title ="会员导出";
//            File file = new File(title + ".xlsx");
//            OutputStream outXlsx = new FileOutputStream(file);
//            ExcelUtils.exportExcelX(title, headMap, list, null, 0, outXlsx);
//            ServletContext context = request.getServletContext();
//            String mimeType = context.getMimeType(file.getPath());
//            if (mimeType == null) {
//                mimeType = "application/octet-stream";
//            }
//            response.setContentType(mimeType);
//            response.setContentLength((int) file.length());
//            response.setHeader("Content-Disposition", "inline;filename=" + toUtf8String(file.getName()));
//            InputStream myStream = new FileInputStream(file.getPath());
//            IOUtils.copy(myStream, response.getOutputStream());
//            outXlsx.flush();
//            outXlsx.close();
//            response.flushBuffer();
//            myStream.close();
//            System.gc();
//            if (file.delete()) {
//                System.out.println("删除成功！");
//            } else {
//                System.out.println("删除失败");
//            }
//            ;
//        }catch (Exception e){
//            log.error(e.getMessage(),e);
//        }
//    }
//
//    @RequestMapping(value = "/export/memberDown", method = {RequestMethod.GET})
//    @ApiOperation(value = "会员模板下载", notes = "会员模板下载")
//    public void memberDown(HttpServletRequest request, HttpServletResponse response) {
//        try{
//
//            JSONArray list = new JSONArray();
//            Map<String, String> headMap = new LinkedHashMap<String, String>();
//            headMap.put("memberName", "*会员名称");
//            headMap.put("qq", "*qq");
//            headMap.put("mobile", "手机");
//            headMap.put("groupName", "群组");
//            headMap.put("address", "地址");
//            headMap.put("remark", "备注");
////            headMap.put("status", "状态");
////            headMap.put("createTime", "创建时间");
//            String title ="会员导入模板";
//            File file = new File(title + ".xlsx");
//            OutputStream outXlsx = new FileOutputStream(file);
//            ExcelUtils.exportExcelModel(title, headMap, null, 0, outXlsx);
//
//            ServletContext context = request.getServletContext();
//            String mimeType = context.getMimeType(file.getPath());
//            if (mimeType == null) {
//                mimeType = "application/octet-stream";
//            }
//            response.setContentType(mimeType);
//            response.setContentLength((int) file.length());
//            response.setHeader("Content-Disposition", "inline;filename=" + toUtf8String(file.getName()));
//            InputStream myStream = new FileInputStream(file.getPath());
//            IOUtils.copy(myStream, response.getOutputStream());
//            outXlsx.flush();
//            outXlsx.close();
//            response.flushBuffer();
//            myStream.close();
//            System.gc();
//            if (file.delete()) {
//                System.out.println("删除成功！");
//            } else {
//                System.out.println("删除失败");
//            }
//            ;
//        }catch (Exception e){
//            log.error(e.getMessage(),e);
//        }
//    }
//
//    @RequestMapping(value = "/import/memberImport", method = {RequestMethod.POST})
//    @ApiOperation(value = "会员模板导入", notes = "会员模板导入")
//    public CommonResult memberImport(@RequestParam(value = "excelFile", required = false) MultipartFile excelFile, HttpServletRequest request, HttpServletResponse response) {
//        if (null == excelFile) {
//            return CommonResult.failed("模板文件为空,请选择文件");
//        }
//        try{
//            String[] keys= {"memberName","qq","mobile","groupName","address","remark"};
//            List<Map<String, String>> excelData = parseGlobalExcelNew(excelFile, keys);
//            for (int i = 0; i < excelData.size(); i++) {
//                System.out.println(excelData.get(i));
//            }
//            int i = pdMemberService.saveByExcel(excelData);
//            return CommonResult.success(i);
//        }catch (Exception e){
//            log.error(e.getMessage(),e);
//        }
//        return CommonResult.success();
//    }
//
//    public static List<Map<String, String>> parseGlobalExcelNew(MultipartFile excelFile, String[] keys) {
//        List<Map<String, String>> data = new ArrayList<Map<String, String>>();
//        try {
//            String fileName = excelFile.getOriginalFilename();//report.xls
//            InputStream fis = excelFile.getInputStream();
//            String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
//            if ("XLS".equals(fileExt.toUpperCase())) {
//                HSSFWorkbook book = new HSSFWorkbook(fis);
//                int sheetNum = book.getNumberOfSheets();
//                for (int j = 0; j < sheetNum; j++) {
//                    HSSFSheet sheet = book.getSheetAt(j);
//                    int firstRow = sheet.getFirstRowNum();
//                    int lastRow = sheet.getLastRowNum();
//                    //除去表头和第一行
//
//                    HSSFRow titleRow = sheet.getRow(0);
//                    if (titleRow == null) {
//                        continue;
//                    }
//                    int titleLength = titleRow.getLastCellNum();
//                    List<String> excelTitle = new ArrayList<>();
//                    for (int i = 0; i < titleLength; i++) {
//                        HSSFCell cellKey = titleRow.getCell(i);
//                        if (cellKey == null) continue;
//                        excelTitle.add(getCellValue(cellKey));
//                    }
//                    int beginRow = firstRow + 2;//开始读取的数据行数
//
//                    for (int i = beginRow; i <= lastRow; i++) {
//                        HSSFRow row = sheet.getRow(i);
//                        Map map = new HashMap();
//                        int rowNum = row.getLastCellNum();
//                        for (int c = 0; c <= rowNum; c++) {
//                            HSSFCell cellKey = row.getCell(c);
//                            if (cellKey == null) continue;
//                            String val = getCellValue(cellKey);
//                            map.put(keys[c], val);
//                        }
//                        data.add(map);
//                    }
//                }
//            } else if ("XLSX".equals(fileExt.toUpperCase())) {
//                XSSFWorkbook book = new XSSFWorkbook(fis);
//                int sheetNum = book.getNumberOfSheets();
//                for (int j = 0; j < sheetNum; j++) {
//                    XSSFSheet sheet = book.getSheetAt(j);
//                    int firstRow = sheet.getFirstRowNum();
//                    int lastRow = sheet.getLastRowNum();
//                    //除去表头和第一行
//
//                    XSSFRow titleRow = sheet.getRow(0);
//                    if (titleRow == null) {
//                        continue;
//                    }
//                    int titleLength = titleRow.getLastCellNum();
//                    List<String> excelTitle = new ArrayList<>();
//                    for (int i = 0; i < titleLength; i++) {
//                        XSSFCell cellKey = titleRow.getCell(i);
//                        if (cellKey == null) continue;
//                        excelTitle.add(getCellValue(cellKey));
//                    }
//                    int beginRow = firstRow + 2;//开始读取的数据行数
//                    for (int i = beginRow; i <= lastRow; i++) {
//                        XSSFRow row = sheet.getRow(i);
//                        Map map = new HashMap();
//                        int rowNum = row.getLastCellNum();
//
//                        for (int c = 0; c <= rowNum; c++) {
//                            XSSFCell cellKey = row.getCell(c);
//                            if (cellKey == null) continue;
//                            String val = getCellValue(cellKey);
//
//                            map.put(keys[c], val);
//                        }
//                        data.add(map);
//                    }
//                }
//            }
//        } catch (IOException e) {
//            e.printStackTrace();
//            return null;
//        }
//        return data;
//    }
//
//
//    private static String getCellValue(HSSFCell cell) {
//        if (cell == null)
//            return "";
//        if (cell.getCellType() == CellType.STRING) {
//            return cell.getStringCellValue();
//        } else if (cell.getCellType() == CellType.BOOLEAN) {
//            return String.valueOf(cell.getBooleanCellValue());
//        } else if (cell.getCellType() == CellType.FORMULA) {
//            return cell.getCellFormula();
//        } else if (cell.getCellType() == CellType.NUMERIC) {
//            short format = cell.getCellStyle().getDataFormat();
//            System.out.println("format:" + format + ";;;;;value:" + cell.getNumericCellValue());
//            SimpleDateFormat sdf = null;
//            if (format == 14 || format == 31 || format == 57 || format == 58
//                    || (176 <= format && format <= 178) || (182 <= format && format <= 196)
//                    || (210 <= format && format <= 213) || (208 == format)) { // 日期
//                if (String.valueOf(cell.getNumericCellValue()).endsWith(".0")) {
//                    sdf = new SimpleDateFormat("yyyy-MM-dd");
//                } else {
//                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                }
//            } else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间
//                sdf = new SimpleDateFormat("HH:mm");
//            } else { // 不是日期格式
//                return Double.valueOf(cell.getNumericCellValue()).longValue() + "";
//            }
//            double value = cell.getNumericCellValue();
//            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
//            if (date == null || "".equals(date)) {
//                return "";
//            }
//            String result = "";
//            try {
//                result = sdf.format(date);
//            } catch (Exception e) {
//                return "";
//            }
//            return result;
//        }
//        return "";
//    }
//
//    private static String getCellValue(XSSFCell cell) {
//        if (cell == null)
//            return "";
//        if (cell.getCellType() == CellType.STRING) {
//            return cell.getStringCellValue();
//        } else if (cell.getCellType() == CellType.BOOLEAN) {
//            return String.valueOf(cell.getBooleanCellValue());
//        } else if (cell.getCellType() == CellType.FORMULA) {
//            return cell.getCellFormula();
//        } else if (cell.getCellType() == CellType.NUMERIC) {
//            short format = cell.getCellStyle().getDataFormat();
//            DataFormatter dataFormatter = new DataFormatter();
//            String strCell = dataFormatter.formatCellValue(cell);
//           // System.out.println("format:" + format + ";value:" + cell.getNumericCellValue()+"--str:"+String.valueOf(cell.getNumericCellValue()));
//            SimpleDateFormat sdf = null;
//            if (format == 14 || format == 31 || format == 57 || format == 58
//                    || (176 <= format && format <= 178) || (182 <= format && format <= 196)
//                    || (210 <= format && format <= 213) || (208 == format)) { // 日期
//                if (String.valueOf(cell.getNumericCellValue()).endsWith(".0")) {
//                    sdf = new SimpleDateFormat("yyyy-MM-dd");
//                } else {
//                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                }
//            } else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间
//                sdf = new SimpleDateFormat("HH:mm");
//            } else { // 不是日期格式
//
//                System.out.println("----strCell::"+strCell);
//                return strCell;
//            }
//            return strCell;
//        }
//        return "";
//    }
//
//
//    private JSONObject info(int currentSize,int failSize,int successSize,int totalSize,String message){
//        JSONObject result = new JSONObject();
//        result.put("currentSize",currentSize);
//        result.put("failSize",failSize);
//        result.put("successSize",successSize);
//        result.put("totalSize",totalSize);
//        if(UtilValidate.isNotEmpty(message)){
//            result.put("message",message);
//        }
//
//        return result;
//    }
//
//    public static void main(String args[]) {
////        String[] keys= {"memberName","qq","mobile","groupName","address","remark"};
////        List<Map<String, String>> result = parseGlobalExcelNew(new File("C:\\Users\\刘庆峰\\Desktop\\文件\\会员导入模板 (2).xls"), keys);
////        for (int i = 0; i < result.size(); i++) {
////
////        }
//    }
//
//}
